package com.example.demo.common;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.util.StringUtils;

//设置宽度和高度
//row.setHeightInPoints(30);//设置行的高度
//sheet.setColumnWidth(0, 20 * 256);//设置列的宽度

////渲染单元格
//cell.setCellStyle(style);
public class GenerateExcelUtil {

	private static Logger log = LoggerFactory.getLogger(GenerateExcelUtil.class);

	/**
	 * 导出xlsx格式excel
	 * 
	 * @param workbook
	 * @param sheetNum
	 * @param sheetTitle
	 * @param headers
	 * @param result
	 * @param out
	 * @throws Exception
	 */
	public void exportExcel(XSSFWorkbook workbook, int sheetNum, String sheetTitle, String[] headers,
			List<List<String>> result, OutputStream out) throws Exception {
		// 第一步，创建一个webbook，对应一个Excel以xsl为扩展名文件
		XSSFSheet sheet = workbook.createSheet();
		workbook.setSheetName(sheetNum, sheetTitle);
		// 设置列宽度大小
		sheet.setDefaultColumnWidth((short) 20);
		// 第二步， 生成表格第一行的样式和字体
		XSSFCellStyle style = workbook.createCellStyle();

		// 设置背景
		style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		style.setFillForegroundColor(IndexedColors.PALE_BLUE.index);

		// 加边框
		style.setBorderBottom(BorderStyle.THIN);// 下边框
		style.setBorderLeft(BorderStyle.THIN);// 左边框
		style.setBorderRight(BorderStyle.THIN);// 右边框
		style.setBorderTop(BorderStyle.THIN); // 上边框

		// 居中
		style.setAlignment(HorizontalAlignment.CENTER);// 水平居中
		style.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中

		// 设置字体
		XSSFFont font = workbook.createFont();
		font.setFontName("华文行楷");// 设置字体名称
		font.setColor(HSSFColor.BLACK.index); // 设置字体颜色

		// 设置字体所在的行高度
		font.setFontHeightInPoints((short) 20); // 设置字号

		font.setBold(true);// 设置是否加粗
		font.setItalic(false);// 设置是否为斜体

		// 把字体应用到当前的样式
		style.setFont(font);
		// 指定当单元格内容显示不下时自动换行
		style.setWrapText(true);
		// 产生表格标题行
		XSSFRow row = sheet.createRow(0);
		for (int i = 0; i < headers.length; i++) {
			XSSFCell cell = row.createCell((short) i);
			cell.setCellStyle(style);
			XSSFRichTextString text = new XSSFRichTextString(headers[i]);
			cell.setCellValue(text.toString());
		}
		// 第三步：遍历集合数据，产生数据行，开始插入数据
		if (result != null) {
			int index = 1;
			for (List<String> m : result) {
				row = sheet.createRow(index);
				int cellIndex = 0;
				for (String str : m) {
					XSSFCell cell = row.createCell((int) cellIndex);
					// 非空判断
					if (StringUtils.isEmpty(str)) {
						cell.setCellValue("");
					} else {
						cell.setCellValue(str.toString());
					}

					cellIndex++;
				}
				index++;
			}
		}

	}

	/**
	 * 导出xls格式excel
	 * 
	 * @param workbook
	 * @param sheetNum
	 * @param sheetTitle
	 * @param headers
	 * @param result
	 * @param out
	 * @throws Exception
	 */
	public void exportExcel(HSSFWorkbook workbook, int sheetNum, String sheetTitle, String[] headers,
			List<List<String>> result) throws Exception {
		// 第一步，创建一个webbook，对应一个Excel以xsl为扩展名文件
		HSSFSheet sheet = workbook.createSheet();
		workbook.setSheetName(sheetNum, sheetTitle);
		// 设置列宽度大小
		sheet.setDefaultColumnWidth((short) 20);
		// 第二步， 生成表格第一行的样式和字体
		HSSFCellStyle style = workbook.createCellStyle();

		// 设置背景
		style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		style.setFillForegroundColor(IndexedColors.PALE_BLUE.index);

		// 加边框
		style.setBorderBottom(BorderStyle.THIN);// 下边框
		style.setBorderLeft(BorderStyle.THIN);// 左边框
		style.setBorderRight(BorderStyle.THIN);// 右边框
		style.setBorderTop(BorderStyle.THIN); // 上边框

		// 居中
		style.setAlignment(HorizontalAlignment.CENTER);// 水平居中
		style.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中

		// 设置字体
		HSSFFont font = workbook.createFont();
		// font.setFontName("华文行楷");// 设置字体名称
		// font.setColor(HSSFColor.BLACK.index); // 设置字体颜色

		// 设置字体所在的行高度
		// font.setFontHeightInPoints((short) 20); // 设置字号

		// font.setBold(true);// 设置是否加粗
		font.setItalic(false);// 设置是否为斜体

		// 把字体应用到当前的样式
		style.setFont(font);
		// 指定当单元格内容显示不下时自动换行
		style.setWrapText(true);
		// 产生表格标题行
		HSSFRow row = sheet.createRow(0);
		for (int i = 0; i < headers.length; i++) {
			HSSFCell cell = row.createCell((short) i);
			cell.setCellStyle(style);
			XSSFRichTextString text = new XSSFRichTextString(headers[i]);
			cell.setCellValue(text.toString());
		}
		// 第三步：遍历集合数据，产生数据行，开始插入数据
		if (result != null) {
			int index = 1;
			for (List<String> m : result) {
				row = sheet.createRow(index);
				int cellIndex = 0;
				for (String str : m) {
					HSSFCell cell = row.createCell((int) cellIndex);
					// 非空判断
					if (StringUtils.isEmpty(str)) {
						cell.setCellValue("");
					} else {
						cell.setCellValue(str.toString());
					}
					cellIndex++;
				}
				index++;
			}
		}

	}

	public static String getDateString(Date date, String format) {
		SimpleDateFormat timeFormatter = new SimpleDateFormat(format);
		String dateString = timeFormatter.format(date);
		return dateString;
	}

	/**
	 * @param 导出xlsx格式excel
	 */
	public static ResponseEntity<byte[]> exportexcel(XSSFWorkbook workBook, String excelName,
			HttpServletRequest request, HttpServletResponse response) {
		HttpHeaders headers = new HttpHeaders();
		try {
			if (workBook != null) {
				String fileName = new String((excelName).getBytes("utf-8"), "utf-8");
				// OutputStream os = response.getOutputStream();
				headers.setContentDispositionFormData("attachment", fileName);
				headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
				ByteArrayOutputStream bos = new ByteArrayOutputStream();
				workBook.write(bos);
				bos.flush();
				bos.close();
				return new ResponseEntity<byte[]>(bos.toByteArray(), headers, HttpStatus.OK);
			} else {
				log.info(excelName + "报表生成失败");
				System.out.println(excelName + "报表生成失败");
			}
		} catch (IOException e) {
			log.info("IO Exception while read excel" + excelName + "template.", e);
			System.out.println("IO Exception while read excel" + excelName + "template. " + e.getMessage());
		} finally {
		}
		return null;
	}

	/**
	 * @param 导出xls格式excel
	 */
	public static void exportexcel(HSSFWorkbook workBook, String excelName, HttpServletRequest request,
			HttpServletResponse response) {

		try {
			if (workBook != null) {
				String timeStamp = getDateString(new Date(), "yyyyMMddHHmm");

				String fileName = new String((timeStamp + excelName + ".xls").getBytes("gb2312"), "ISO8859-1");

				response.setContentType("application/vnd.ms-excel");
				response.setHeader("Content-Disposition", "attachment;filename=\"" + fileName + "\"");
				OutputStream os = response.getOutputStream();
				workBook.write(os);
				os.flush();
				os.close();

			} else {
				log.info(excelName + "报表生成失败");
				System.out.println(excelName + "报表生成失败");
			}

		} catch (IOException e) {
			log.info("IO Exception while read excel" + excelName + "template.", e);
			System.out.println("IO Exception while read excel" + excelName + "template. " + e.getMessage());
		} finally {
		}
	}

}